package com.chilli.cosima.data.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class DbTools
{
	// private static Connection dbConn = null;

	private static Log log = LogFactory.getLog(DbTools.class);

	public DbTools()
	{

	}

	/**
	 * 通过查询sql语句查询数据 同时把数据保存在List中 每一条数据保存在hashtable中
	 * 
	 * @param sql
	 *            查询的语句
	 * @return List
	 * @throws ConnectException
	 */
	@SuppressWarnings("unchecked")
	public static List select(String sql)
	{

		Connection poolConn = null;
		Statement statementVo = null;
		ResultSet rs = null;
		String colName = "";
		List vret = new Vector(15, 5);
		try
		{

			poolConn = DBManager.getInstance().getConnection();
			int iCol = 0;
			statementVo = poolConn.createStatement();
			rs = statementVo.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			iCol = rsmd.getColumnCount();

			// 循环数据行
			while (rs.next())
			{
				Hashtable ht = new Hashtable();

				// 循环数据列
				for (int i = 1; i <= iCol; i++)
				{
					colName = rsmd.getColumnName(i).toLowerCase();
					Object obj = rs.getObject(colName);
					if (obj == null)
					{
						ht.put(colName, "");
					} else
					{
						ht.put(colName, obj);
					}
				}

				// 把每条数据保存到List中
				vret.add(ht);

			}
		} catch (Exception ex)
		{
			log.error(ex.getMessage(), ex);
		} finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (statementVo != null)
				{
					statementVo.close();
					statementVo = null;
				}

			} catch (Exception e1)
			{
				log.error(e1.getMessage(), e1);
			}

			// 释放连接
			if (null != poolConn)
			{
				try
				{
					DBManager.getInstance().closeConnection(poolConn);
				} catch (Exception e)
				{
					log.error(e.getMessage(), e);
				}
			}

		}
		return vret;
	}

	/**
	 * 通过查询sql语句查询数据 同时把数据保存在List中 查询单个列使用该方法 selet a from b
	 * 
	 * @param sql
	 *            查询的语句
	 * @return List
	 * @throws ConnectException
	 */
	public static List<String> selectCol(String sql)
	{

		List<String> vret = new Vector<String>(15, 5);

		Statement statementVo = null;
		ResultSet rs = null;
		Connection dbConn = null;
		try
		{
			dbConn = DBManager.getInstance().getConnection();

			statementVo = dbConn.createStatement();
			rs = statementVo.executeQuery(sql);

			// 循环数据行
			while (rs.next())
			{
				// 循环数据列
				Object obj = rs.getObject(1);
				vret.add(obj.toString());
			}
		} catch (Exception ex)
		{
			log.error(sql);
			log.error(ex.getMessage(), ex);

		} finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (statementVo != null)
				{
					statementVo.close();
					statementVo = null;
				}
			} catch (Exception e1)
			{
				log.error(e1.getMessage(), e1);
			}

			if (null != dbConn)
			{
				try
				{
					DBManager.getInstance().closeConnection(dbConn);
				} catch (Exception e)
				{
					log.error(e.getMessage(), e);
				}
			}
		}
		return vret;
	}

	public static int updatez(String sql)
	{
		Statement stmt = null;
		int ret = 0;

		Connection poolConn = null;

		try
		{
			poolConn = DBManager.getInstance().getConnection();

			poolConn.setAutoCommit(false);
			stmt = poolConn.createStatement();
			ret = stmt.executeUpdate(sql);
			poolConn.commit();
		} catch (Exception ex)
		{
			try
			{
				if (poolConn != null)
				{
					poolConn.rollback();
				}
			} catch (SQLException sqlex1)
			{
				log.error(sqlex1.getMessage(), sqlex1);

			}
			ret = -1;
		} finally
		{
			try
			{
				if (stmt != null)
				{
					stmt.close();
					stmt = null;
				}
			} catch (Exception e2)
			{
				log.error(e2.getMessage(), e2);
			}

			if (null != poolConn)
			{
				try
				{
					DBManager.getInstance().closeConnection(poolConn);
				} catch (Exception e)
				{
					log.error(e.getMessage(), e);
				}
			}
		}
		return ret;
	}

	public static int update(String sql)
	{
		Statement stmt = null;
		int ret = 0;

		Connection poolConn = null;

		try
		{
			poolConn = DBManager.getInstance().getConnection();

			poolConn.setAutoCommit(false);
			stmt = poolConn.createStatement();
			stmt.executeUpdate(sql);
			poolConn.commit();
			ret = 0;
		} catch (Exception ex)
		{
			log.error(ex.getMessage(), ex);
			try
			{
				if (poolConn != null)
				{
					poolConn.rollback();
				}
			} catch (SQLException e1)
			{
				log.error(e1.getMessage(), e1);

			}
			ret = -1;
		} finally
		{
			try
			{
				if (stmt != null)
				{
					stmt.close();
					stmt = null;
				}
			} catch (Exception e2)
			{
				log.error(e2.getMessage(), e2);
			}

			if (null != poolConn)
			{
				try
				{
					DBManager.getInstance().closeConnection(poolConn);
				} catch (Exception e)
				{
					log.error(e.getMessage(), e);
				}
			}
		}
		return ret;
	}

	public static void delete(String sql)
	{
		Statement stmt = null;
		// int ret = 0;

		Connection poolConn = null;

		try
		{
			poolConn = DBManager.getInstance().getConnection();

			poolConn.setAutoCommit(false);
			stmt = poolConn.createStatement();
			stmt.execute(sql);
			poolConn.commit();
			// ret = 0;
		} catch (Exception ex)
		{
			log.error(ex.getMessage(), ex);
			try
			{
				if (poolConn != null)
				{
					poolConn.rollback();
				}
			} catch (SQLException e1)
			{
				log.error(e1.getMessage(), e1);

			}
			// ret = -1;
		} finally
		{
			try
			{
				if (stmt != null)
				{
					stmt.close();
					stmt = null;
				}
			} catch (Exception e2)
			{
				log.error(e2.getMessage(), e2);
			}

			if (null != poolConn)
			{
				try
				{
					DBManager.getInstance().closeConnection(poolConn);
				} catch (Exception e)
				{
					log.error(e.getMessage(), e);
				}
			}
		}
		// return ret;
	}

	/*
	 * 
	 * 批量将信息插入数据库中 传入SQL及批量一次的号段信息List（如：200个） 此处号段信息处理为数据库中为String型的情况
	 * 
	 */
	@SuppressWarnings("unchecked")
	public static void insertBatchInfo(String sql, List oneBatchList)
	{
		Connection poolConn = null;
		PreparedStatement stmta = null;
		try
		{
			poolConn = DBManager.getInstance().getConnection();
			poolConn.setAutoCommit(false);

			stmta = poolConn.prepareStatement(sql);

			for (int i = 0; i < oneBatchList.size(); i++)
			{
				String num = String.valueOf(oneBatchList.get(i));
				stmta.setString(1, num);
				stmta.addBatch();
			}
			stmta.executeBatch();
			poolConn.commit();
		} catch (Exception ex)
		{
			log.error(ex.getMessage(), ex);
			try
			{
				if (poolConn != null)
				{
					poolConn.rollback();
				}
			} catch (SQLException e1)
			{
				log.error(e1.getMessage(), e1);
			}
		} finally
		{
			if (null != stmta)
			{
				try
				{
					stmta.close();
				} catch (SQLException e)
				{
					log.error(e.getMessage(), e);
				}
			}
			if (null != poolConn)
			{
				try
				{
					DBManager.getInstance().closeConnection(poolConn);
				} catch (Exception e)
				{
					log.error(e.getMessage(), e);
				}
			}
		}

	}

	/*
	 * 
	 * 批量将信息插入数据库中 传入SQL及批量一次的号段信息List（如：200个） 此处信息处理为数据库中为String型的情况,并存在两个变量
	 * 
	 */
	@SuppressWarnings("unchecked")
	public static void insertTwoBatchInfo(String sql, List oneBatchList)
	{

		Connection poolConn = null;
		PreparedStatement stmta = null;
		try
		{
			poolConn = DBManager.getInstance().getConnection();
			poolConn.setAutoCommit(false);

			stmta = poolConn.prepareStatement(sql);

			for (int i = 0; i < oneBatchList.size(); i++)
			{
				String num = String.valueOf(oneBatchList.get(i));
				String[] numlevel = num.split(",");
				stmta.setString(1, numlevel[0]);
				stmta.setString(2, numlevel[1]);
				stmta.addBatch();
			}
			stmta.executeBatch();
			poolConn.commit();
		} catch (Exception ex)
		{
			log.error(ex.getMessage(), ex);
			try
			{
				if (poolConn != null)
				{
					poolConn.rollback();
				}
			} catch (SQLException e1)
			{
				log.error(e1.getMessage(), e1);
			}
		} finally
		{
			if (null != stmta)
			{
				try
				{
					stmta.close();
				} catch (SQLException e)
				{
					log.error(e.getMessage(), e);
				}
			}
			if (null != poolConn)
			{
				try
				{
					DBManager.getInstance().closeConnection(poolConn);
				} catch (Exception e)
				{
					log.error(e.getMessage(), e);
				}
			}
		}

	}

	/**
	 * @param sql
	 * @param conList
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static String getSqlByConlist(String sql, List conList)
	{
		int listSize = conList.size();
		StringBuffer strBuffer = new StringBuffer();
		strBuffer = strBuffer.append(sql);

		for (int i = 0; i < listSize; i++)
		{
			String id = (String) conList.get(i);

			if ((listSize - 1) != i)
			{
				strBuffer = strBuffer.append(id);
				strBuffer = strBuffer.append("','");
			} else
			{
				strBuffer = strBuffer.append(id);
				strBuffer = strBuffer.append("')");
			}

		}
		return strBuffer.toString();
	}

	/**
	 * @param sql
	 * @param paras
	 * @return List
	 */
	@SuppressWarnings("unchecked")
	public static List query(String Strsql, Object paras[])
	{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Statement statementVo = null;
		String colName = "";
		List vret = new Vector(15, 5);
		int iCol = 0;

		try
		{
			conn = DBManager.getInstance().getConnection();
			stmt = conn.prepareStatement(Strsql);

			if (paras != null)
			{
				for (int i = 0; i < paras.length; i++)
					stmt.setObject(i + 1, paras[i]);

			}
			rs = stmt.executeQuery();

			// 循环数据行
			ResultSetMetaData rsmd = rs.getMetaData();
			iCol = rsmd.getColumnCount();

			while (rs.next())
			{
				Hashtable ht = new Hashtable();

				// 循环数据列
				for (int i = 1; i <= iCol; i++)
				{
					colName = rsmd.getColumnName(i).toLowerCase();
					Object obj = rs.getObject(colName);
					if (obj == null)
					{
						ht.put(colName, "");
					} else
					{
						ht.put(colName, obj);
					}
				}

				// 把每条数据保存到List中
				vret.add(ht);
			}
		} catch (SQLException ex)
		{
			log.error(ex.getMessage(), ex);
		} finally
		{
			try
			{
				if (null != rs)
				{
					rs.close();
					rs = null;
				}
				if (null != stmt)
				{
					stmt.close();
					stmt = null;
				}
				if (null != statementVo)
				{
					statementVo.close();
					statementVo = null;
				}

			} catch (Exception e1)
			{
				log.error(e1.getMessage(), e1);
			}

			// 释放连接
			if (conn != null)
			{
				try
				{
					DBManager.getInstance().closeConnection(conn);
				} catch (Exception e)
				{
					log.error(e.getMessage(), e);
				}
			}

		}
		return vret;

	}

}
